#!/usr/bin/python3
# -*- coding: UTF-8 -*-

from auto_report.auto_report import AutoReport
import os
from openpyxl import load_workbook
from auto_report.utils import info, warning, read_from_xlsx, get_font, find_row
from rich import print
import shutil
from openpyxl.utils.cell import coordinate_from_string

# 每个湖数据的区域
THREE_LAKE_VALUE_MAP = {
    '抚仙湖': 'A8:BM27',
    '星云湖': 'A28:BM55',
    '杞麓湖': 'A56:BM86',
}

# 整个表的数据区域，用于拆分
TARGET_AREA = 'A8:BM86'

# 索引列，项目名称列号，0索引
PRIMARY_COL = 2
# 修改开始列
MOD_START_COL = 4
# 修改结束列
MOD_END_COL = 64


TEXT_RED = get_font('ff0000', 12)
TEXT_BLACK = get_font('000000', 12)


class HuPoGeMingReport(AutoReport):
    """docstring for LakeReport"""

    def __init__(self, target_month):
        super(HuPoGeMingReport, self).__init__(target_month)
        self.target_month = target_month
        self.report_name = '“一湖一策”项目投资情况统计表'

    def get_src_file(self):
        return f'玉溪市-{self.get_last_month_field()}-{self.report_name}.xlsx'

    def get_dst_file(self):
        return f'玉溪市-{self.get_month_field()}-{self.report_name}.xlsx'

    def generate(self):
        src_file = self.get_src_file() if os.path.exists(
            self.get_src_file()) else f'{self.report_name}.xlsx'

        target_wb = load_workbook(src_file)
        target_ws = target_wb.active

        src_folder = f'geming/{self.target_month}'
        files = self.get_files(src_folder)
        for f in files:
            info('处理', f'{src_folder}/{f}')
            incoming_data = read_from_xlsx(f'{src_folder}/{f}', row_offset=4)
            info(f'数据行数：{len(incoming_data)}')
            lake_name = incoming_data[0][29] if len(
                incoming_data) > 0 and incoming_data[0][29] else ''
            if not lake_name in THREE_LAKE_VALUE_MAP:
                continue

            one_lake_mod_area = target_ws[THREE_LAKE_VALUE_MAP.get(lake_name)]
            for rowIndex, r in enumerate(one_lake_mod_area):
                mat = find_row(incoming_data, r, PRIMARY_COL)
                if mat != None:
                # if r[PRIMARY_COL].value == incoming_data[rowIndex][PRIMARY_COL]:
                    for colIndex in range(MOD_START_COL, MOD_END_COL):
                        newVal = incoming_data[rowIndex][colIndex]
                        if r[colIndex].value != newVal:
                            warning(
                                f'修改 {r[colIndex].coordinate}, {r[colIndex].value} -> {newVal}')
                            r[colIndex].value = newVal
                            r[colIndex].font = TEXT_RED
                        else:
                            r[colIndex].font = TEXT_BLACK

        dst_file = self.get_dst_file()
        target_ws['A1'].value = f'{self.report_name}（{self.get_month_field()}）'
        target_wb.save(dst_file)

        print('完成', dst_file)

    def split(self):
        split_file = self.get_dst_file() if os.path.exists(
            self.get_dst_file()) else f'{self.report_name}.xlsx'
        if (split_file == None):
            warning('错误！找不到文件', self.dst_file)
            return
        info('拆分', split_file)
        target_dir = 'dst'
        if os.path.exists(target_dir):
            shutil.rmtree(target_dir)
        os.mkdir(target_dir)

        from_wb = load_workbook(split_file)
        all_row_start = coordinate_from_string(
            TARGET_AREA.split(':')[0])[1]
        all_row_end = coordinate_from_string(
            TARGET_AREA.split(':')[1])[1]
        info('数据区域起止行', all_row_start, all_row_end)
        lakes = set(THREE_LAKE_VALUE_MAP.keys())

        for index, key in enumerate(lakes):
            one_lake_file = f'{target_dir}/{index}-{key}-{self.get_month_field()}-{self.report_name}.xlsx'
            from_wb.save(one_lake_file)

            info('处理', one_lake_file)
            start_row = coordinate_from_string(
                THREE_LAKE_VALUE_MAP.get(key).split(':')[0])[1]
            end_row = coordinate_from_string(
                THREE_LAKE_VALUE_MAP.get(key).split(':')[1])[1]
            info(start_row, end_row)

            # row_num = end_row - start_row
            before_range = (all_row_start, start_row-all_row_start)
            after_range = (end_row+1, all_row_end-end_row)
            info(before_range, after_range)

            new_wb = load_workbook(one_lake_file)
            new_ws = new_wb.active

            # unmerge_list = []
            # for mcr in new_ws.merged_cells:
            #     if mcr.min_row > 6 and mcr.max_col < 3:
            #         unmerge_list.append(mcr.coord)
            # print(unmerge_list)
            # for area in unmerge_list:
            #     new_ws.unmerge_cells(area)
            # new_wb.save(one_lake_file)

            # new_wb = load_workbook(one_lake_file)
            # new_ws = new_wb.active

            if after_range[1] > 0:
                new_ws.delete_rows(after_range[0], after_range[1])
            if before_range[1] > 0:
                new_ws.delete_rows(before_range[0], before_range[1])

            new_wb.save(one_lake_file)

            new_wb = load_workbook(one_lake_file)
            new_ws = new_wb.active

            one_lake_mod_area = new_ws[TARGET_AREA]
            for _, r in enumerate(one_lake_mod_area):
                print('sa', len(r), range(MOD_START_COL, MOD_END_COL))
                for colIndex in range(MOD_START_COL, MOD_END_COL):
                    r[colIndex].font = TEXT_BLACK
            for _, r in enumerate(one_lake_mod_area):
                for colIndex in range(MOD_START_COL, MOD_END_COL):
                    r[colIndex].font = TEXT_BLACK
            # new_ws.merge_cells(f'A7:A{7 + row_num}')
            # new_ws.merge_cells(f'B7:B{7 + row_num}')

            # for i in range(7+row_num+1, all_row_end+1):
            #     new_ws.row_dimensions[i].height = 18

            new_wb.save(one_lake_file)

            # openAndSave(one_lake_file)

        info('完成')


    def statistic(self):
        import pandas as pd
        # dst_file = self.get_dst_file()
        dst_file = 'aa.xlsx'
        print('statistic', dst_file)
        data = pd.read_excel(dst_file, sheet_name=0,header=6,)
        print(data[data.columns[2]])



